package com.wa.base.dataPermission;

import cn.hutool.core.collection.CollectionUtil;
import com.wa.util.CommUtils;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.select.*;
import org.apache.commons.collections4.CollectionUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.util.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p> Madison.lu </p>
 * <p>Version: 1.0.0</p>
 * <p>Create Date： 2019/10/30 14:43 </p>
 * <p>Copyright (c) 2017 ~ 2019 Allchips版权所有</p>
 */
public class QueryFilterSqlSplicerUtil {


    /**
     * 日志
     */
    private static final Logger logger = LoggerFactory.getLogger(QueryFilterSqlSplicerUtil.class);


    /**
     * sql拼接
     *
     * @param sql                 原sql语句
     * @param queryFilterGroupMap page对象携带的额外参数
     * @param params              page对象携带的额外参数
     * @return String 拼接完毕的sql语句
     */
    public static String spliceSql(String sql, Map<String, List<QueryFilterRuleTree>> queryFilterGroupMap, Map<String
            , String> params) throws Exception {

        if (CollectionUtil.isEmpty(queryFilterGroupMap)) {
            // 没有条件，返回原sql
            return sql;
        }
        if (CommUtils.isNull(params)) {
            // 没有变量，创建一个防止空指针
            params = new HashMap<>();
        }
        QueryFilterRuleTranslator queryFilterRulTranslator = new QueryFilterRuleTranslator(params);

        // 判断是否是ORDER语句，有则把order语句截取出来再处理，防组件无法处理convert函数导致报错
        String sqlLeft = sql;
        String sqlRight = "";
        int orderIndex = sql.toLowerCase().indexOf("order");
        int convertIndex = sql.toLowerCase().indexOf("convert");
        if (orderIndex > 0 && convertIndex > 0) {
            sqlLeft = sql.substring(0, orderIndex);
            sqlRight = sql.substring(orderIndex - 1);

            String sqlAfterOrder = sql.substring(orderIndex + 5);
            if (sqlAfterOrder.toLowerCase().indexOf("order") > 0) {
                logger.error("QueryFilterSqlSplicerUtil.spliceSql：原sql语句存在CONVERT函数及两次ORDER，jsqlparser无法处理！");
                throw new Exception("QueryFilterSqlSplicerUtil.spliceSql：原sql语句存在CONVERT函数及两次ORDER，jsqlparser无法处理！");
            }
        }


        // sql转select对象，便于操
        Select select = (Select) CCJSqlParserUtil.parse(sqlLeft);
        try {
            List<PlainSelect> plainSelectList = new ArrayList<>();

            SelectBody selectBody = select.getSelectBody();
            if (selectBody instanceof PlainSelect) {
                plainSelectList.add((PlainSelect) selectBody);
            } else if (selectBody instanceof SetOperationList) {
                getSelectList((SetOperationList) selectBody, plainSelectList);
            }

            // 获取所有权限表名
            Map<String, String> tableNameMap = new HashMap<>();
            queryFilterGroupMap.forEach((tableName, treeList) -> {
                tableNameMap.put(tableName, tableName);
            });
            for (PlainSelect plainSelect : plainSelectList) {
                // 多表权限sql拼接效率低于单表权限sql拼接，确定用户带多表权限且sql是多表查询，才启用
                if (tableNameMap.size() > 1 && isMultiTableSelect(plainSelect, tableNameMap)) {
                    logger.info("用户带多表权限，启用sql拼接2.0=================sql:{}", plainSelect.toString());
                    // 如果本sql是最内层包含多表权限条件的sql，直接在本sql加入所有条件
                    if (isInnermostMultiTableSelect(plainSelect, tableNameMap)) {
                        // 添加权限条件
                        setMultiTableCondition(plainSelect, queryFilterGroupMap, queryFilterRulTranslator, tableNameMap);
                    } else {
                        // 多表权限sql拼接
                        multiTableSpliceSql(plainSelect, queryFilterGroupMap, queryFilterRulTranslator, tableNameMap);
                    }
                    logger.info("用户带多表权限，启用sql拼接2.0=================resultSql:{}", plainSelect.toString());
                } else {
                    // 单表权限sql拼接
                    spliceSelect(plainSelect, queryFilterGroupMap, queryFilterRulTranslator);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("QueryFilterSqlSplicerUtil.spliceSql:{}", e.getMessage());
            throw new Exception("QueryFilterSqlSplicerUtil.spliceSql:" + e.getMessage());
        }
        return select.toString() + sqlRight;
    }

    /**
     * 递归获取所有 PlainSelect 列表
     *
     * @param setOperationList
     * @param plainSelectList
     * @return
     */
    public static List<PlainSelect> getSelectList(SetOperationList setOperationList, List<PlainSelect> plainSelectList) {

        List<SelectBody> selects = setOperationList.getSelects();
        for (SelectBody body : selects) {
            if (body instanceof PlainSelect) {
                plainSelectList.add((PlainSelect) body);
            }else if (body instanceof SetOperationList) {
                getSelectList((SetOperationList) body, plainSelectList);
            }
        }
        return plainSelectList;
    }


    /**
     * 单表权限sql拼接
     *
     * @param plainSelect              传入的PlainSelect对象
     * @param queryFilterGroupMap      待拼接的条件对象
     * @param queryFilterRulTranslator sql翻译器
     */
    private static void spliceSelect(PlainSelect plainSelect, Map<String, List<QueryFilterRuleTree>> queryFilterGroupMap
            , QueryFilterRuleTranslator queryFilterRulTranslator) throws Exception {

        // 解决第一个查询对象
        if (plainSelect.getFromItem() instanceof Table) {
            // 如果查询对象表，添加查询条件
            String tableName = ((Table) plainSelect.getFromItem()).getName().replace("`", "");
            // 获取规则组对象列表
            List<QueryFilterRuleTree> queryFilterGroups = queryFilterGroupMap.get(tableName);
            if (CommUtils.isNotNull(queryFilterGroups)) {
                // 获取表别名。没有则默认表名
                String mainTableAlias = tableName;
                try {
                    mainTableAlias = plainSelect.getFromItem().getAlias().getName();
                } catch (Exception e) {
                    logger.debug("当前sql中， " + mainTableAlias + " 没有设置别名");
                }
                //添加查询条件
                setCondition(plainSelect, queryFilterRulTranslator, queryFilterGroups, mainTableAlias);
            }
        } else if (plainSelect.getFromItem() instanceof SubSelect) {
            // 如果，查询对象是个子查询
            SubSelect subSelect = (SubSelect) plainSelect.getFromItem();

            SelectBody selectBody = subSelect.getSelectBody();
            List<PlainSelect> plainSelectList = new ArrayList<>();

            if (selectBody instanceof PlainSelect) {
                plainSelectList.add((PlainSelect) selectBody);
            } else if (selectBody instanceof SetOperationList) {
                getSelectList((SetOperationList) selectBody, plainSelectList);
            }
            for (PlainSelect currentPlainSelect : plainSelectList) {
                // 递归添加查询条件
                spliceSelect(currentPlainSelect, queryFilterGroupMap, queryFilterRulTranslator);
            }
        }
        // 获取连接表，遍历解决连接表
        List<Join> joins = plainSelect.getJoins();
        if (CommUtils.isNull(joins)) {
            return;
        }
        for (Join join : joins) {
            if (join.getRightItem() instanceof Table) {
                String tableName = ((Table) join.getRightItem()).getName().replace("`", "");
                // 获取规则组对象列表
                List<QueryFilterRuleTree> queryFilterGroups = queryFilterGroupMap.get(tableName);
                if (CommUtils.isNotNull(queryFilterGroups)) {
                    // 获取表别名
                    String joinTableAlias = tableName;
                    try {
                        joinTableAlias = join.getRightItem().getAlias().getName();
                    } catch (Exception e) {
                        logger.debug("当前sql中， " + joinTableAlias + " 没有设置别名");
                    }
                    //添加查询条件
                    setCondition(plainSelect, queryFilterRulTranslator, queryFilterGroups, joinTableAlias);
                }
            } else if (join.getRightItem() instanceof SubSelect) {
                // 如果，查询对象是个子查询
                SubSelect subSelect = (SubSelect) join.getRightItem();

                SelectBody selectBody = subSelect.getSelectBody();
                List<PlainSelect> plainSelectList = new ArrayList<>();

                if (selectBody instanceof PlainSelect) {
                    plainSelectList.add((PlainSelect) selectBody);
                } else if (selectBody instanceof SetOperationList) {
                    getSelectList((SetOperationList) selectBody, plainSelectList);
                }
                for (PlainSelect currentPlainSelect : plainSelectList) {
                    // 递归添加查询条件
                    spliceSelect(currentPlainSelect, queryFilterGroupMap, queryFilterRulTranslator);
                }
            }
        }
        plainSelect.setJoins(joins);
    }

    /**
     * 给select语句添加条件
     *
     * @param plainSelect              select对象
     * @param queryFilterRulTranslator sql翻译器
     * @param queryFilterGroups        条件对象列表
     * @param tableName                表别名
     */
    private static void setCondition(PlainSelect plainSelect, QueryFilterRuleTranslator queryFilterRulTranslator,
                                     List<QueryFilterRuleTree> queryFilterGroups, String tableName) throws Exception {

        // 翻译得到条件sql
        String conditionStr = queryFilterRulTranslator.translateTreeList(queryFilterGroups, tableName);

        if (CommUtils.isEmpty(conditionStr)) {
            return;
        }
        // 判断原sql语句是否已有where关键字
        if (plainSelect.getWhere() == null) {
            plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(conditionStr));
        } else {
            AndExpression and = new AndExpression(plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(conditionStr));
            plainSelect.setWhere(and);
        }
    }

    /**
     * 判断sql是否是多表查询语句
     *
     * @param plainSelect  sql对象最
     * @param tableNameMap 表名Map
     * @return Boolean 是否存在
     */
    private static boolean isMultiTableSelect(PlainSelect plainSelect, Map<String, String> tableNameMap) throws Exception {

        // 获取所有查询表中，是数据权限控制表的表名
        HashSet<String> nameSet = new HashSet<>();
        getTableNameSet(plainSelect, tableNameMap, nameSet);
        // 获取的表名大于一个，则是多表查询语句
        return nameSet.size() > 1;
    }

    /**
     * 多表权限sql拼接
     *
     * @param plainSelect              原sql对象
     * @param queryFilterGroupMap      数据权限集合
     * @param queryFilterRulTranslator sql翻译器
     * @param tableNameMap             需加权限控制表名集合
     * @return 结果sql
     */
    private static void multiTableSpliceSql(PlainSelect plainSelect, Map<String, List<QueryFilterRuleTree>> queryFilterGroupMap
            , QueryFilterRuleTranslator queryFilterRulTranslator, Map<String, String> tableNameMap) throws Exception {

        // 获得本sql的查询表（子查询）列表
        List<FromItem> fromItemList = getFromItemList(plainSelect);
        for (FromItem currentFromItem : fromItemList) {
            if (!(currentFromItem instanceof SubSelect)) {
                // 如果不是子查询，则处理下一句
                continue;
            }
            SelectBody selectBody = ((SubSelect) currentFromItem).getSelectBody();
            List<PlainSelect> plainSelectList = new ArrayList<>();
            if (selectBody instanceof PlainSelect) {
                plainSelectList.add((PlainSelect) selectBody);
            } else if (selectBody instanceof SetOperationList) {
                getSelectList((SetOperationList) selectBody, plainSelectList);
            }
            for (PlainSelect currentPlainSelect : plainSelectList) {
                if (!(isMultiTableSelect(currentPlainSelect, tableNameMap))) {
                    // 不是多表查询语句，则处理下一句
                    continue;
                }
                if (isInnermostMultiTableSelect(currentPlainSelect, tableNameMap)) {
                    // 子查询sql是最内层包含多表权限条件的sql，直接在本sql加入所有条件
                    setMultiTableCondition(currentPlainSelect, queryFilterGroupMap, queryFilterRulTranslator, tableNameMap);
                } else {
                    // 递归
                    multiTableSpliceSql(currentPlainSelect, queryFilterGroupMap, queryFilterRulTranslator, tableNameMap);
                }
            }
        }
    }

    /**
     * 给select语句添加多表权限条件
     *
     * @param plainSelect              原sql对象
     * @param queryFilterGroupMap      数据权限集合
     * @param queryFilterRulTranslator sql翻译器
     * @param tableNameMap             需加权限控制表名集合
     * @throws Exception
     */
    private static void setMultiTableCondition(PlainSelect plainSelect, Map<String, List<QueryFilterRuleTree>> queryFilterGroupMap
            , QueryFilterRuleTranslator queryFilterRulTranslator, Map<String, String> tableNameMap) throws Exception {

        // 获得本sql的查询表（子查询）列表
        List<FromItem> fromItemList = getFromItemList(plainSelect);

        // 获取所有需拼接的条件列表

        Map<String,List<String>> conditionMap = new HashMap<>();

        for (FromItem currentFromItem : fromItemList) {
            if (currentFromItem instanceof Table) {
                String tableName = ((Table) currentFromItem).getName().replace("`", "");
                // 不同表的条件分开存，同表不同别名用 and 不同表用 or
                List<String> conditionList = conditionMap.get(tableName);
                if (CollectionUtils.isEmpty(conditionList)) {
                    conditionList = new ArrayList<>();
                    conditionMap.put(tableName,conditionList);
                }
                // 获取规则组对象列表
                List<QueryFilterRuleTree> queryFilterGroups = queryFilterGroupMap.get(tableName);
                if (CommUtils.isNotNull(queryFilterGroups)) {
                    // 获取表别名。没有则默认表名
                    String mainTableAlias = tableName;
                    try {
                        mainTableAlias = currentFromItem.getAlias().getName();
                    } catch (Exception e) {
                        logger.info("当前sql中， " + mainTableAlias + " 没有设置别名");
                    }
                    // 获取条件sql
                    String conditionSql = queryFilterRulTranslator.translateTreeList(queryFilterGroups, mainTableAlias);
                    if (CommUtils.isNotEmpty(conditionSql)) {
                        conditionList.add(conditionSql);
                    }
                }
            } else if (currentFromItem instanceof SubSelect) {
                HashSet<String> tableNameSet = new HashSet<>();

                SelectBody selectBody = ((SubSelect) currentFromItem).getSelectBody();
                List<PlainSelect> plainSelectList = new ArrayList<>();
                if (selectBody instanceof PlainSelect) {
                    plainSelectList.add((PlainSelect) selectBody);
                } else if (selectBody instanceof SetOperationList) {
                    getSelectList((SetOperationList) selectBody, plainSelectList);
                }
                for (PlainSelect currentPlainSelect : plainSelectList) {
                    getTableNameSet(currentPlainSelect , tableNameMap, tableNameSet);
                }

                String mainTableAlias = currentFromItem.getAlias().getName();
                for (String tableName : tableNameSet) {
                    // 不同表的条件分开存，同表不同别名用 and 不同表用 or
                    List<String> conditionList = conditionMap.get(tableName);
                    if (CollectionUtils.isEmpty(conditionList)) {
                        conditionList = new ArrayList<>();
                        conditionMap.put(tableName,conditionList);
                    }
                    // 获取规则组对象列表
                    List<QueryFilterRuleTree> queryFilterGroups = queryFilterGroupMap.get(tableName);
                    if (CommUtils.isNotNull(queryFilterGroups)) {
                        // 获取条件sql
                        String conditionSql = queryFilterRulTranslator.translateTreeList(queryFilterGroups, mainTableAlias);
                        if (CommUtils.isNotEmpty(conditionSql)) {
                            conditionList.add(conditionSql);
                        }
                    }
                }
            }
        }
        // 给sql添加条件



            StringBuilder stringBuilder = new StringBuilder();
            // 用OR拼接所有条件
            conditionMap.forEach((tableName,conditionList)-> {

                StringBuilder stringBuilderOneTable = new StringBuilder();
                for (String currentCondition : conditionList) {
                    if (CommUtils.isNotEmpty(currentCondition) && CommUtils.isNotEmpty(stringBuilderOneTable.toString())) {
                        // 同表不同别名，用 AND 连接
                        stringBuilderOneTable.append(" AND ");
                    }
                    stringBuilderOneTable.append(currentCondition);
                }
                if (CommUtils.isNotEmpty(stringBuilderOneTable.toString()) && CommUtils.isNotEmpty(stringBuilder.toString())) {
                    // 如果本轮条件已之前条件都不为空，用OR连接
                    stringBuilder.append(" OR ");
                }
                stringBuilder.append(stringBuilderOneTable);
            });
            // 如果结果为空，返回
            if (CommUtils.isEmpty(stringBuilder.toString())) {
                return;
            } else {
                stringBuilder.insert(0, "(");
                stringBuilder.append(")");
            }
            // 判断原sql语句是否已有where关键字
            if (plainSelect.getWhere() == null) {
                plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(stringBuilder.toString()));
            } else {
                Expression expression = CCJSqlParserUtil.parseCondExpression(stringBuilder.toString());

                AndExpression and = new AndExpression(plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(stringBuilder.toString()));
                plainSelect.setWhere(and);
            }

    }

    /**
     * 判断sql本层查询是否是最内层多表查询语句
     *
     * @param plainSelect  sql对象最
     * @param tableNameMap 表名Map
     * @return Boolean 是否存在
     */
    private static boolean isInnermostMultiTableSelect(PlainSelect plainSelect, Map<String, String> tableNameMap) throws Exception {

        // 获得本sql的查询表（子查询）列表
        List<FromItem> fromItemList = getFromItemList(plainSelect);

        // 获取在数据权限表Map中，且是本sql查询的所有表名或在子查询的表名
        HashMap<Integer, HashSet<String>> resultMap = new HashMap<>();
        for (int i = 0; i < fromItemList.size(); i++) {
            FromItem currentFromItem = fromItemList.get(i);
            HashSet<String> tableNameSet = new HashSet<>();
            if (currentFromItem instanceof Table) {
                String tableName = ((Table) currentFromItem).getName().replace("`", "");
                if (CommUtils.isNotEmpty(tableNameMap.get(tableName))) {
                    tableNameSet.add(tableName);
                }
            } else if (currentFromItem instanceof SubSelect) {
                SelectBody selectBody = ((SubSelect) currentFromItem).getSelectBody();
                List<PlainSelect> plainSelectList = new ArrayList<>();
                if (selectBody instanceof PlainSelect) {
                    plainSelectList.add((PlainSelect) selectBody);
                } else if (selectBody instanceof SetOperationList) {
                    getSelectList((SetOperationList) selectBody, plainSelectList);
                }
                for (PlainSelect currentPlainSelect : plainSelectList) {
                    getTableNameSet(currentPlainSelect , tableNameMap, tableNameSet);
                }
            }
            resultMap.put(i, tableNameSet);
        }

        // 分析表在各子查询中分布情况，判断本sql是否是多表查询的最内层sql
        HashMap<String, Integer> analyzeMap = new HashMap<>();
        Set<Integer> resultKeySet = resultMap.keySet();
        // 遍历每局子查询或表的表名
        for (Integer key : resultKeySet) {
            HashSet<String> value = resultMap.get(key);
            ArrayList<String> tableNameList = new ArrayList<>(value);
            if (tableNameList.size() == 0) {
                // 如果本轮子查询没有表名，直接下一轮
                continue;
            }
            int size = analyzeMap.size();
            if (size == 0) {
                // 如果之前的子查询没有表名，放入本轮所有表名
                for (String tableName : tableNameList) {
                    analyzeMap.put(tableName, key);
                }
            } else if (size == 1) {
                if (tableNameList.size() > 1) {
                    // 如果之前有一个表名，而本轮有多个，直接返回true
                    return true;
                } else {
                    // 如果之前与本轮都有一个表名，判断是否是同一个，不是返回true
                    String tableName = tableNameList.get(0);
                    if (CommUtils.isNull(analyzeMap.get(tableName))) {
                        return true;
                    }
                }
            } else {
                // 如果之前有多个表名，且本轮有值，返回true
                return true;
            }
        }
        return false;
    }

    /**
     * 获取当前sql包含的指定表名
     *
     * @param plainSelect  sql对象
     * @param tableNameMap 表名集合
     * @param tableNameSet sql包含指定表名集合
     */
    private static void getTableNameSet(PlainSelect plainSelect, Map<String, String> tableNameMap,
                                        HashSet<String> tableNameSet) throws Exception {

        // 解决第一个查询对象
        if (plainSelect.getFromItem() instanceof Table) {
            // 如果查询对象表，判断并加入表名
            String tableName = ((Table) plainSelect.getFromItem()).getName().replace("`", "");
            if (CommUtils.isNotNull(tableNameMap.get(tableName))) {
                tableNameSet.add(tableName);
            }
        } else if (plainSelect.getFromItem() instanceof SubSelect) {
            // 如果，查询对象是个子查询
            SubSelect subSelect = (SubSelect) plainSelect.getFromItem();
            SelectBody selectBody = subSelect.getSelectBody();
            List<PlainSelect> plainSelectList = new ArrayList<>();
            if (selectBody instanceof PlainSelect) {
                plainSelectList.add((PlainSelect) selectBody);
            } else if (selectBody instanceof SetOperationList) {
                getSelectList((SetOperationList) selectBody, plainSelectList);
            }
            for (PlainSelect currentPlainSelect : plainSelectList) {
                // 递归获得表名
                getTableNameSet(currentPlainSelect, tableNameMap, tableNameSet);
            }
        }
        // 获取连接表，遍历解决连接表
        List<Join> joins = plainSelect.getJoins();
        if (CommUtils.isNull(joins)) {
            return;
        }
        for (Join join : joins) {
            if (join.getRightItem() instanceof Table) {
                // 如果查询对象表，判断并加入表名
                String tableName = ((Table) join.getRightItem()).getName().replace("`", "");
                if (CommUtils.isNotNull(tableNameMap.get(tableName))) {
                    tableNameSet.add(tableName);
                }
            } else if (join.getRightItem() instanceof SubSelect) {
                // 如果，查询对象是个子查询
                SubSelect subSelect = (SubSelect) join.getRightItem();
                SelectBody selectBody = subSelect.getSelectBody();
                List<PlainSelect> plainSelectList = new ArrayList<>();
                if (selectBody instanceof PlainSelect) {
                    plainSelectList.add((PlainSelect) selectBody);
                } else if (selectBody instanceof SetOperationList) {
                    getSelectList((SetOperationList) selectBody, plainSelectList);
                }
                for (PlainSelect currentPlainSelect : plainSelectList) {
                    // 递归获得表名
                    getTableNameSet(currentPlainSelect, tableNameMap, tableNameSet);
                }
            }
        }
    }

    /**
     * 获得本sql的查询表（子查询）列表
     *
     * @param plainSelect
     * @return
     */
    private static List<FromItem> getFromItemList(PlainSelect plainSelect) {

        List<FromItem> fromItemList = new ArrayList<>();
        FromItem fromItem = plainSelect.getFromItem();
        fromItemList.add(fromItem);
        List<Join> joins = plainSelect.getJoins();
        if (CollectionUtils.isNotEmpty(joins)) {
            for (Join join : joins) {
                FromItem rightItem = join.getRightItem();
                fromItemList.add(rightItem);
            }
        }
        return fromItemList;
    }

}
